package com.bruce.dao.impl;

import com.bruce.Common.Resultcode;
import com.bruce.bean.*;
import com.bruce.bean.Dto.CourseplanDto;
import com.bruce.bean.Dto.GradeDto;
import com.bruce.bean.Dto.SelectCourseDto;
import com.bruce.dao.CourseDao;
import com.bruce.utils.PageUtils;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @author cc
 * @date 2021/7/26 9:59
 * @Description
 */

public class CourseDaoImpl extends BaseDao implements CourseDao{
    @Override
    public PageUtils FindAllCourse(PageUtils pageUtils) {
        String sql = "select * from course";


        List<Course> coursesall=new LinkedList<>();
        List<Course> returnCourse =new LinkedList<>();
        try {
             coursesall = queryRunner.query(sql, new BeanListHandler<Course>(Course.class));
               pageUtils = new PageUtils(pageUtils.getPagesize(),pageUtils.getPageIndex(),coursesall.size());
              sql = "select * from course limit ?,?";
             returnCourse = queryRunner.query(sql,new BeanListHandler<Course>(Course.class),(pageUtils.getPageIndex()-1)*pageUtils.getPagesize()+1,pageUtils.getPagesize());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        pageUtils.setRecords(returnCourse);
        return pageUtils;
    }

    @Override
    public Boolean isisChoiced(String cid,String sid) {
        String sql = "select id from sc where cid=? and sid =?";
        try {
            Integer id =(Integer) queryRunner.query(sql, new ScalarHandler<>(), cid, sid);
            if (id==null||id==0||id.equals(null)){
                return true;
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }

        return false;
    }

    @Override
    public Boolean CourseIsTeacher(int cid) {
        String sql = "select tid from courseplan where cid =?";

        try {
            String query = queryRunner.query(sql, new ScalarHandler<>(), Integer.toString(cid));
            if (query==null||query.equals(null)||query.equals("")){
                return false;
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return true;
    }

    @Override
    public Courseplan FindCourse(int cid) {
        String sql = "select *  from courseplan where cid =?";
        Courseplan courseplan = new Courseplan();
        String s = Integer.toString(cid);
        try {
            courseplan  = (Courseplan) queryRunner.query(sql, new BeanHandler<Courseplan>(Courseplan.class), s);
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return courseplan;
    }

    @Override
    public Course findCoursToname(int cid) {
        String sql = "select * from course where cid = ?";
        Course course = new Course();
        try {
            course = queryRunner.query(sql, new BeanHandler<Course>(Course.class), Integer.toString(cid));
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return course;
    }

    @Override
    public int addCoursetoSC(String cid, String sid) {
        String sql = "insert into sc (cid,sid) values(?,?)";
        int i=0;
        try {
            i = (Integer)queryRunner.update(sql,cid,sid);
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return i;
    }

    @Override
    public PageUtils<CourseplanDto> FindCourseByStudentid(String sid,int pageindex) {
        List<Sc> scs = FindSidAndCidByStudentid(sid);
        List<CourseplanDto> courseplans = new ArrayList<>();
        PageUtils pageUtils = null;

        StringBuffer sql = new StringBuffer("select courseplan.*,cname,tname \n" +
                "from courseplan,course,teacher \n" +
                "where  courseplan.cid=course.Cid and courseplan.tid=teacher.Tid  and courseplan.cid in ");
        if (scs.size()<1){
            throw new  RuntimeException(Resultcode.Select_no.getMess());
        }

        //拼接sql
        sql = Append_sql(scs, sql);

        try {
            Object[] prams = GetCid(scs);

            List<CourseplanDto> query = queryRunner.query(sql.toString(), new BeanListHandler<CourseplanDto>(CourseplanDto.class),prams);
            pageUtils = new PageUtils(5,pageindex,query.size());

            String limit = " limit ?,?";
             limit = sql.append(limit).toString();


            Object[] temp = getnewObjects(pageUtils, prams);

//            courseplans =  queryRunner.query(limit,new BeanListHandler<Courseplan>(Courseplan.class),prams,(pageUtils.getPageIndex()-1)*2,pageUtils.getPagesize());
            courseplans =  queryRunner.query(limit,new BeanListHandler<CourseplanDto>(CourseplanDto.class),temp);

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        pageUtils.setRecords(courseplans);
        return pageUtils;
    }

    private Object[] getnewObjects(PageUtils pageUtils, Object[] prams) {
        Object[] temp = new Object[prams.length+2];
        for (int i = 0; i < prams.length; i++) {
            temp[i]=prams[i];
        }
        temp[prams.length] = (pageUtils.getPageIndex()-1)*2;
        temp[prams.length+1] = pageUtils.getPagesize();
        return temp;
    }

    private StringBuffer Append_sql(List<Sc> scs, StringBuffer sql) {
        //拼接sql
        sql.append("(");
        for (Sc sc : scs) {
            StringBuffer temp = new StringBuffer("?,");
            sql.append(temp);
        }
        sql.append(")");
        sql.deleteCharAt(sql.lastIndexOf(","));
        return sql;
    }

    private Object[] GetCid(List<Sc> scs) {
        ArrayList a = new ArrayList();
        for (Sc sc : scs) {
            a.add(sc.getCid());
        }
        return a.toArray();
    }

    @Override
    public List<Sc> FindSidAndCidByStudentid(String sid) {
        String sql = "select * from Sc where sid = ?";
        List<Sc> sc = new ArrayList<>();
        try {
            sc = queryRunner.query(sql, new BeanListHandler<Sc>(Sc.class), sid);

        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return sc;
    }

    @Override
    public int ExitCouese(String cid, String sid) {
        String sql = "DELETE from sc where cid=? and sid=?";
        int i = 0;
        try {
             i = queryRunner.update(sql, cid, sid);
            if (i<1){
                throw  new RuntimeException(Resultcode.Delect_err.getMess());
            }
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return i;
    }

    @Override
    public PageUtils<GradeDto> FindOwnGrade(String sid, int parseInt) {
        StringBuffer sql = new StringBuffer("SELECT grade.*,course.Cname from course,grade where course.Cid = grade.cid and sid =? ");
        List<GradeDto> gradeDtos = new ArrayList<>();
        PageUtils<GradeDto> gradeDtoPageUtils = null;
        try {
             gradeDtos = queryRunner.query(sql.toString(), new BeanListHandler<GradeDto>(GradeDto.class), sid);
             gradeDtoPageUtils = new PageUtils<GradeDto>(5,parseInt,gradeDtos.size());
             sql = sql.append("limit ?,?");
             gradeDtos = queryRunner.query(sql.toString(), new BeanListHandler<GradeDto>(GradeDto.class), sid, (gradeDtoPageUtils.getPageIndex() - 1) * gradeDtoPageUtils.getPageIndex(), gradeDtoPageUtils.getPagesize());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        gradeDtoPageUtils.setRecords(gradeDtos);
        return gradeDtoPageUtils;
    }

    @Override
    public int InsertIntoCoursepla(Courseplan doAddcouForm) {
        String  sql = "insert into courseplan values (?,?,?,?,?,?,?,?,?)";
        int i = 0;
        try {
            i=queryRunner.update(sql,doAddcouForm.getCourseclass(),doAddcouForm.getClassroom(),doAddcouForm.getCourseweek(),doAddcouForm.getCid(),doAddcouForm.getTid(),doAddcouForm.getClassroom(),doAddcouForm.getCredits(),doAddcouForm.getPeriod(),doAddcouForm.getTotalnum());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return  i;
    }

    @Override
    public PageUtils<CourseplanDto> FindCourseByTeacheid(String tid, int parseInt) {
        StringBuffer sql = new StringBuffer("select courseplan.*,cname,tname \n" +
                "from courseplan,course,teacher \n" +
                "where  courseplan.cid=course.Cid and courseplan.tid=teacher.Tid  and courseplan.tid = ? ");
        List<CourseplanDto> courseplanDtos = null;
       PageUtils  pageUtils =new PageUtils<>();
        try {
            courseplanDtos = queryRunner.query(sql.toString(), new BeanListHandler<CourseplanDto>(CourseplanDto.class), tid);
            pageUtils = new  PageUtils<CourseplanDto>(5,parseInt,courseplanDtos.size());
            sql = sql.append("limit ?,?");
            courseplanDtos = queryRunner.query(sql.toString(),new BeanListHandler<CourseplanDto>(CourseplanDto.class),tid,(pageUtils.getPageIndex()-1)*pageUtils.getPagesize(),pageUtils.getPagesize());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        pageUtils.setRecords(courseplanDtos);
        return pageUtils;
    }

    @Override
    public int UpdataCourse(Courseplan doAddcouForm) {
        StringBuffer sql = new StringBuffer("update courseplan set Courseclass=?,coursetime=?,courseweek=?,classroom=?,credits=?,period=?,Totalnum=?  where cid =?");
        int update = 0;
        try {
             update = queryRunner.update(sql.toString(), doAddcouForm.getCourseclass(), doAddcouForm.getCoursetime(), doAddcouForm.getCourseweek(), doAddcouForm.getClassroom(), doAddcouForm.getCredits(), doAddcouForm.getPeriod(), doAddcouForm.getTotalnum(),doAddcouForm.getCid());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        return update;
    }

    @Override
    public PageUtils<SelectCourseDto> FindSelecdCourse(String tid, String pageIndex) {
        StringBuffer sql = new StringBuffer("\n" +
                "select course.cid,cname,Totalnum,COUNT(sc.id) as stuSum,tid\n" +
                "from course,courseplan,sc\n" +
                "where course.Cid = courseplan.cid and courseplan.cid = sc.cid \n" +
                "GROUP BY sc.cid HAVING courseplan.tid =?");
        List<SelectCourseDto> selectCourseDtos = null;
        PageUtils<SelectCourseDto> pageUtils = new PageUtils<>();
        try {
             selectCourseDtos = queryRunner.query(sql.toString(), new BeanListHandler<SelectCourseDto>(SelectCourseDto.class), tid);
             pageUtils = new PageUtils<SelectCourseDto>(5,Integer.parseInt(pageIndex),selectCourseDtos.size());
             sql = sql.append("limit ?,?");
            selectCourseDtos =  queryRunner.query(sql.toString(),new BeanListHandler<SelectCourseDto>(SelectCourseDto.class),tid,(pageUtils.getPageIndex()-1)*pageUtils.getPagesize(),pageUtils.getPagesize());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        pageUtils.setRecords(selectCourseDtos);
        return pageUtils;
    }

    @Override
    public PageUtils<Student> FindStudentbyTeacheIdAndCourseId(String tid, String cid, String pageIndex) {
        StringBuffer sql = new StringBuffer("SELECT  student.* from sc,courseplan,student\n" +
                "where courseplan.cid = sc.cid and sc.sid = student.Sid \n" +
                "and courseplan.tid= ? and sc.cid = ?");
        PageUtils pageUtils = new PageUtils();
        List<Student> students = null;
        try {
            students = queryRunner.query(sql.toString(), new BeanListHandler<Student>(Student.class), tid, cid);
            pageUtils = new PageUtils(5,Integer.parseInt(pageIndex),students.size());
            sql = sql.append("limit ?,?");
            students= queryRunner.query(sql.toString(),new BeanListHandler<Student>(Student.class),tid,cid,(pageUtils.getPageIndex()-1)*pageUtils.getPagesize(),pageUtils.getPagesize());
        } catch (SQLException sqlException) {
            sqlException.printStackTrace();
        }
        pageUtils.setRecords(students);
        return pageUtils;
    }
}
